/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pooproj.repositorio;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import pooproj.basica.Locacao;
import pooproj.erro.ConexaoException;
import pooproj.erro.ConsultaException;
import pooproj.util.GerenciadorConexao;
import pooproj.util.GerenciadorConexaoImpl;

/**
 *
 * @author Daniella
 */
public class RepositorioLocacaoImpl implements RepositorioLocacao {
    
    private GerenciadorConexao gc = GerenciadorConexaoImpl.getInstancia();

    @Override
    public void inserir(Locacao l) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "INSERT INTO locacao (dtLoc, dtDevol, codCliente) VALUES (?,?,?)";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1,l.getDtLoc());
            pstm.setString(2,l.getDtDevol());
            pstm.setInt(3,l.getCliente().getCodCliente());
            pstm.executeUpdate();
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
    }

    @Override
    public void alterar(Locacao l) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "UPDATE locacao SET dtDevol=? WHERE codLoc=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1,l.getDtDevol());
            pstm.setInt(2,l.getCodLoc());
            pstm.executeUpdate();
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
    }

    @Override
    public void excluir(Locacao l) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "DELETE FROM locacao  WHERE codLoc=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,l.getCodLoc());
            pstm.executeUpdate();
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
    }

    @Override
    public Locacao consultarDtLoc(String data) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        Locacao l = null;
        String sql = "SELECT locacao.codLoc, locacao.dtLoc, locacao.dtDevol, locacao.codCliente, cliente.nomeCliente FROM locacao, cliente  WHERE locacao.codCliente=cliente.codCliente AND locacao.dtLoc=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1,data);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
                l.setDtLoc(rs.getString("dtLoc"));
                l.setDtDevol(rs.getString("dtDevol"));
                l.getCliente().setCodCliente(rs.getInt("codCliente"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        return l;
    }

    @Override
    public int consultarTudo(String dtLoc, String dtDevol, int codCliente) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        Locacao l = null;
        String sql = "SELECT codLoc FROM locacao WHERE dtLoc=? AND dtDevol=? AND codCliente=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1,dtLoc);
            pstm.setString(2,dtDevol);
            pstm.setInt(3,codCliente);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        return l.getCodLoc();
    }



    @Override
    public Locacao consultarDtDevol(String data) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        Locacao l = null;
        String sql = "SELECT locacao.codLoc, locacao.dtLoc, locacao.dtDevol, locacao.codCliente, cliente.nomeCliente FROM locacao, cliente  WHERE locacao.codCliente=cliente.codCliente AND locacao.dtDevol=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1,data);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
                l.setDtLoc(rs.getString("dtLoc"));
                l.setDtDevol(rs.getString("dtDevol"));
                l.getCliente().setCodCliente(rs.getInt("codCliente"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        return l;
    }

    @Override
    public Locacao consultarCodLoc(int cod) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        Locacao l = null;
        String sql = "SELECT locacao.codLoc, locacao.dtLoc, locacao.dtDevol, locacao.codCliente, cliente.nomeCliente FROM locacao, cliente  WHERE locacao.codCliente=cliente.codCliente AND locacao.codLoc=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,cod);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
                l.setDtLoc(rs.getString("dtLoc"));
                l.setDtDevol(rs.getString("dtDevol"));
                l.getCliente().setCodCliente(rs.getInt("codCliente"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        return l;
    }

    @Override
    public Locacao consultarCodCliente(int cod) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        Locacao l = null;
        String sql = "SELECT locacao.codLoc, locacao.dtLoc, locacao.dtDevol, locacao.codCliente, cliente.nomeCliente FROM locacao, cliente  WHERE locacao.codCliente=cliente.codCliente AND locacao.codCliente=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,cod);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
                l.setDtLoc(rs.getString("dtLoc"));
                l.setDtDevol(rs.getString("dtDevol"));
                l.getCliente().setCodCliente(rs.getInt("codCliente"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        return l;
    }
          

    @Override
    public ArrayList<Locacao> listar() throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        ArrayList<Locacao> lista = new ArrayList();
        Locacao l;
        String sql = "SELECT locacao.codLoc, locacao.dtLoc, locacao.dtDevol, locacao.codCliente, cliente.nomeCliente FROM locacao, cliente  WHERE locacao.codCliente=cliente.codCliente";
        try{
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while(rs.next()){
                l = new Locacao();
                l.setCodLoc(rs.getInt("codLoc"));
                l.setDtLoc(rs.getString("dtLoc"));
                l.setDtDevol(rs.getString("dtDevol"));
                l.getCliente().setCodCliente((rs.getInt("codLoc")));
                lista.add(l);
            }
            stm.close();
            rs.close();
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        }
        return lista;
    }
    
}
